package com.example;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.io.File;
import java.sql.ResultSet;

public class DatabaseUtil {
    private static final String DB_NAME = "student_score_tracker.db";
    private static final String URL;

    static {
        try {
            // 获取当前工作目录
            String currentDir = System.getProperty("user.dir");
            System.out.println("当前工作目录: " + currentDir);
            
            // 创建数据库文件路径
            File dbFile = new File(currentDir, DB_NAME);
            System.out.println("数据库文件路径: " + dbFile.getAbsolutePath());
            
            // 确保父目录存在
            File parentDir = dbFile.getParentFile();
            if (parentDir != null && !parentDir.exists()) {
                System.out.println("创建数据库目录: " + parentDir.getAbsolutePath());
                parentDir.mkdirs();
            }
            
            URL = "jdbc:sqlite:" + dbFile.getAbsolutePath();
            System.out.println("数据库URL: " + URL);

            // 加载SQLite驱动
            System.out.println("正在加载SQLite驱动...");
            DriverManager.registerDriver(new org.sqlite.JDBC());
            System.out.println("SQLite驱动加载成功！");

            // 测试数据库连接
            try (Connection conn = DriverManager.getConnection(URL)) {
                System.out.println("数据库连接测试成功！");
            } catch (SQLException e) {
                System.err.println("数据库连接测试失败：" + e.getMessage());
                e.printStackTrace();
                throw new RuntimeException("数据库连接测试失败", e);
            }
        } catch (SQLException e) {
            System.err.println("SQLite驱动加载失败：" + e.getMessage());
            e.printStackTrace();
            throw new RuntimeException("SQLite驱动加载失败", e);
        }
    }

    public static Connection getConnection() throws SQLException {
        Connection conn = null;
        try {
            System.out.println("正在连接数据库：" + URL);
            conn = DriverManager.getConnection(URL);
            System.out.println("数据库连接成功！");
            
            // 测试连接是否有效
            try (Statement stmt = conn.createStatement()) {
                stmt.execute("SELECT 1");
                System.out.println("数据库连接测试成功！");
            }
        } catch (SQLException e) {
            System.err.println("数据库连接失败：" + e.getMessage());
            e.printStackTrace();
            throw e;
        }
        return conn;
    }

    public static void closeConnection(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
                System.out.println("数据库连接已关闭。");
            } catch (SQLException e) {
                System.err.println("关闭数据库连接失败：" + e.getMessage());
                e.printStackTrace();
            }
        }
    }

    public static void initializeDatabase() {
        System.out.println("开始初始化数据库...");
        try (Connection conn = getConnection()) {
            // 创建院系表
            conn.createStatement().execute(
                "CREATE TABLE IF NOT EXISTS departments (" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "name TEXT NOT NULL," +
                "description TEXT" +
                ")"
            );

            // 创建专业表
            conn.createStatement().execute(
                "CREATE TABLE IF NOT EXISTS majors (" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "name TEXT NOT NULL," +
                "department_id INTEGER," +
                "description TEXT," +
                "FOREIGN KEY (department_id) REFERENCES departments(id)" +
                ")"
            );

            // 创建班级表
            conn.createStatement().execute(
                "CREATE TABLE IF NOT EXISTS classes (" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "name TEXT NOT NULL," +
                "major_id INTEGER," +
                "grade TEXT," +
                "FOREIGN KEY (major_id) REFERENCES majors(id)" +
                ")"
            );

            // 创建用户表
            conn.createStatement().execute(
                "CREATE TABLE IF NOT EXISTS users (" +
                "username TEXT PRIMARY KEY," +
                "password TEXT NOT NULL," +
                "name TEXT NOT NULL," +
                "type TEXT NOT NULL," +
                "status TEXT NOT NULL," +
                "department TEXT," +
                "major TEXT," +
                "class_name TEXT" +
                ")"
            );

            // 创建课程表
            conn.createStatement().execute(
                "CREATE TABLE IF NOT EXISTS courses (" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "name TEXT NOT NULL," +
                "credits REAL NOT NULL," +
                "department TEXT," +
                "description TEXT," +
                "course_type TEXT," +
                "semester TEXT" +
                ")"
            );

            // 创建成绩表
            conn.createStatement().execute(
                "CREATE TABLE IF NOT EXISTS grades (" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "student_username TEXT NOT NULL," +
                "course_name TEXT NOT NULL," +
                "score REAL," +
                "semester TEXT," +
                "course_type TEXT," +
                "exam_time TEXT," +
                "teacher_name TEXT," +
                "FOREIGN KEY (student_username) REFERENCES users(username)" +
                ")"
            );

            // 创建课表
            conn.createStatement().execute(
                "CREATE TABLE IF NOT EXISTS schedules (" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "student_username TEXT NOT NULL," +
                "course_name TEXT NOT NULL," +
                "day_of_week TEXT," +
                "start_time TEXT," +
                "end_time TEXT," +
                "location TEXT," +
                "teacher_name TEXT," +
                "semester TEXT," +
                "FOREIGN KEY (student_username) REFERENCES users(username)" +
                ")"
            );

            // 创建选课表
            conn.createStatement().execute(
                "CREATE TABLE IF NOT EXISTS selected_courses (" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "student_username TEXT NOT NULL," +
                "course_name TEXT NOT NULL," +
                "credits REAL," +
                "semester TEXT," +
                "FOREIGN KEY (student_username) REFERENCES users(username)" +
                ")"
            );

            // 创建教师授课表
            conn.createStatement().execute(
                "CREATE TABLE IF NOT EXISTS taught_courses (" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "teacher_username TEXT NOT NULL," +
                "course_name TEXT NOT NULL," +
                "semester TEXT," +
                "FOREIGN KEY (teacher_username) REFERENCES users(username)" +
                ")"
            );

            // 创建学期表
            conn.createStatement().execute(
                "CREATE TABLE IF NOT EXISTS semesters (" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "name TEXT NOT NULL," +
                "start_date DATE NOT NULL," +
                "end_date DATE NOT NULL," +
                "status TEXT NOT NULL" +
                ")"
            );

            // 插入初始数据
            insertInitialData(conn);
        } catch (SQLException e) {
            System.err.println("Error initializing database: " + e.getMessage());
            e.printStackTrace();
        }
    }

    private static void insertInitialData(Connection conn) throws SQLException {
        // 检查是否已有数据
        ResultSet rs = conn.createStatement().executeQuery("SELECT COUNT(*) FROM users");
        if (rs.next() && rs.getInt(1) > 0) {
            return; // 已有数据，不插入
        }

        // 插入管理员账号
        PreparedStatement pstmt = conn.prepareStatement(
            "INSERT INTO users (username, password, name, type, status) VALUES (?, ?, ?, ?, ?)"
        );
        pstmt.setString(1, "admin");
        pstmt.setString(2, "123456");
        pstmt.setString(3, "管理员");
        pstmt.setString(4, "admin");
        pstmt.setString(5, "active");
        pstmt.executeUpdate();

        // 插入示例学期数据
        pstmt = conn.prepareStatement(
            "INSERT INTO semesters (name, start_date, end_date, status) VALUES (?, ?, ?, ?)"
        );
        
        // 2023-2024学年第一学期
        pstmt.setString(1, "2023-2024-1");
        pstmt.setDate(2, java.sql.Date.valueOf("2023-09-01"));
        pstmt.setDate(3, java.sql.Date.valueOf("2024-01-15"));
        pstmt.setString(4, "已结束");
        pstmt.executeUpdate();

        // 2023-2024学年第二学期
        pstmt.setString(1, "2023-2024-2");
        pstmt.setDate(2, java.sql.Date.valueOf("2024-02-26"));
        pstmt.setDate(3, java.sql.Date.valueOf("2024-07-15"));
        pstmt.setString(4, "进行中");
        pstmt.executeUpdate();

        // 2024-2025学年第一学期
        pstmt.setString(1, "2024-2025-1");
        pstmt.setDate(2, java.sql.Date.valueOf("2024-09-01"));
        pstmt.setDate(3, java.sql.Date.valueOf("2025-01-15"));
        pstmt.setString(4, "未开始");
        pstmt.executeUpdate();
    }

    private static void insertUser(Connection conn, String username, String password, String name, String type, String department, String status) throws SQLException {
        String sql = "INSERT INTO users (username, password, name, type, department, status) VALUES (?, ?, ?, ?, ?, ?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, username);
            pstmt.setString(2, password);
            pstmt.setString(3, name);
            pstmt.setString(4, type);
            pstmt.setString(5, department);
            pstmt.setString(6, status);
            pstmt.executeUpdate();
            System.out.println("插入用户：" + username);
        } catch (SQLException e) {
            System.err.println("插入用户失败：" + username + " - " + e.getMessage());
            throw e;
        }
    }

    private static void insertGrade(Connection conn, String courseName, double score, String semester, String courseType, String studentUsername, String examTime, String teacherName) throws SQLException {
        String sql = "INSERT INTO grades (courseName, score, semester, courseType, studentUsername, examTime, teacherName) VALUES (?, ?, ?, ?, ?, ?, ?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, courseName);
            pstmt.setDouble(2, score);
            pstmt.setString(3, semester);
            pstmt.setString(4, courseType);
            pstmt.setString(5, studentUsername);
            pstmt.setString(6, examTime);
            pstmt.setString(7, teacherName);
            pstmt.executeUpdate();
            System.out.println("插入成绩：" + studentUsername + " - " + courseName);
        } catch (SQLException e) {
            System.err.println("插入成绩失败：" + studentUsername + " - " + courseName + " - " + e.getMessage());
            throw e;
        }
    }

    private static void insertSchedule(Connection conn, String studentUsername, String courseName, String dayOfWeek, String startTime, String endTime, String location, String teacherName, String semester) throws SQLException {
        String sql = "INSERT INTO schedules (studentUsername, courseName, dayOfWeek, startTime, endTime, location, teacherName, semester) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, studentUsername);
            pstmt.setString(2, courseName);
            pstmt.setString(3, dayOfWeek);
            pstmt.setString(4, startTime);
            pstmt.setString(5, endTime);
            pstmt.setString(6, location);
            pstmt.setString(7, teacherName);
            pstmt.setString(8, semester);
            pstmt.executeUpdate();
            System.out.println("插入课表：" + studentUsername + " - " + courseName);
        } catch (SQLException e) {
            System.err.println("插入课表失败：" + studentUsername + " - " + courseName + " - " + e.getMessage());
            throw e;
        }
    }

    private static void insertSelectedCourse(Connection conn, String studentUsername, String courseName, double credits, String semester) throws SQLException {
        String sql = "INSERT INTO selected_courses (studentUsername, courseName, credits, semester) VALUES (?, ?, ?, ?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, studentUsername);
            pstmt.setString(2, courseName);
            pstmt.setDouble(3, credits);
            pstmt.setString(4, semester);
            pstmt.executeUpdate();
            System.out.println("插入已选课程：" + studentUsername + " - " + courseName);
        } catch (SQLException e) {
            System.err.println("插入已选课程失败：" + studentUsername + " - " + courseName + " - " + e.getMessage());
            throw e;
        }
    }

    private static void insertTaughtCourse(Connection conn, String teacherUsername, String courseName, String semester) throws SQLException {
        String sql = "INSERT INTO taught_courses (teacherUsername, courseName, semester) VALUES (?, ?, ?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, teacherUsername);
            pstmt.setString(2, courseName);
            pstmt.setString(3, semester);
            pstmt.executeUpdate();
            System.out.println("插入所授课程：" + teacherUsername + " - " + courseName);
        } catch (SQLException e) {
            System.err.println("插入所授课程失败：" + teacherUsername + " - " + courseName + " - " + e.getMessage());
            throw e;
        }
    }

    public static void main(String[] args) {
        // 简单的测试
        initializeDatabase();
        try (Connection conn = getConnection()) {
            // 连接成功
        } catch (SQLException e) {
            // 连接失败
        }
    }
} 